package repository.impl;

import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeMap;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import repository.PurchaseRepository;
import domain.Confirmation.Confirmated;
import domain.Purchase;
import domain.User;
import domain.exceptions.BigRangeException;


@Repository
public class PurchaseRepositoryImpl extends AbstractHibernateRepository<Purchase> implements PurchaseRepository {
	
	@Autowired
	public PurchaseRepositoryImpl(SessionFactory session){
		super(session);
	}
	
	public Purchase getPurchase(int id) {
		return get(Purchase.class, id);
	}
	
	public void purchaseItem(Purchase purchase){
		//Ni bien se inicia una compra, el estado no es moroso
		purchase.setDebtPending(false);
		save(purchase);
	}
	
	public List<Purchase> getPurchasedItems(User user) {
		String hql = "FROM Purchase WHERE user=? ORDER BY purchaseDate DESC";
		return find(hql, user);
	}

	@Override
	public List<Purchase> getSoldItems(User user) {
		String hql = "FROM Purchase as p WHERE p.item.user=? ORDER BY p.purchaseDate DESC";
		return find(hql, user);
	}
	public List<Purchase> getPurchasedAndSellItems(User user) {
		String hql = "FROM Purchase WHERE user=? OR item.user=? ORDER BY purchaseDate DESC";
		return find(hql, user, user);
	}
	
	@SuppressWarnings("unchecked")
	public Map<Date, Long> getPurchases(Date dateBefore, Date dateAfter) throws BigRangeException{
		long totaldays = (-dateBefore.getTime()+dateAfter.getTime())/ (1000 * 60 * 60 * 24);
		int year,month,day;
		if(totaldays>31){
			throw new BigRangeException();
		}
		
		Map<Long, Long> map = new TreeMap<Long, Long>();
		String hql="SELECT Count(*) as cantidad, extract(YEAR from purchaseDate) as year, extract(MONTH from purchaseDate) as month, extract(DAY from purchaseDate) as day " +
		"FROM Purchase " +
		"WHERE purchaseDate>=? AND purchaseDate<? " +
		"GROUP BY extract(YEAR from purchaseDate),extract(MONTH from purchaseDate),extract(DAY from purchaseDate) " +
		"ORDER BY extract(YEAR from purchaseDate),extract(MONTH from purchaseDate),extract(DAY from purchaseDate)";
		
		Session session = getSession();
		Calendar c = Calendar.getInstance();
		Query query = session.createQuery(hql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		c.setTime(dateAfter);
		c.add(Calendar.DATE, 1);
		query.setParameter(0, dateBefore);
		query.setParameter(1, c.getTime());
		
		
		Iterator<Object> result = query.iterate();

		
		while(result.hasNext()) {
			Map<String, Object> row = (Map<String, Object>)result.next();
			year=(Integer) row.get("year");
			month=(Integer) row.get("month")-1;
			day=(Integer) row.get("day");
			c.set(year, month, day,0,0,0);
			long aux = c.getTime().getTime();
			map.put(aux-aux%1000, (Long)row.get("cantidad"));
		}
		c.setTime(dateBefore);
		while(c.getTime().before(dateAfter) || c.getTime().equals(dateAfter)){
			if(!map.containsKey(c.getTime().getTime())){
				map.put(c.getTime().getTime(),0L);
			}
			c.add(Calendar.DATE, 1);
		}
		Map<Date, Long> newMap = new TreeMap<Date, Long>();
		Set<Entry<Long,Long>> auxMap = map.entrySet();
		for(Entry<Long,Long> e: auxMap){
			newMap.put(new Date(e.getKey()), e.getValue());
		}
		return newMap;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String, Long> getPurchasesByCategory(){
		Map<String, Long> map = new TreeMap<String, Long>();
		String hql="SELECT Count(*) as cantidad, p.item.category.name as categoria " +
		"FROM Purchase AS p " +
		"GROUP BY p.item.category.name " +
		"ORDER BY p.item.category.name";
		
		Session session = getSession();
		Query query = session.createQuery(hql)
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		Iterator <Object> results = query.iterate();
		
		while (results.hasNext()) {
			Map<String, Object> row = (Map<String, Object>)(results.next());
			map.put((String)row.get("categoria"), (Long)row.get("cantidad"));
		}
		
		return map;
	}
	
	
	@SuppressWarnings("unchecked")
	public Set<CustomerReportItem> getMaxBuy(){
		Set<CustomerReportItem> report = new HashSet<CustomerReportItem>();
		Session session = getSession();
		
		String hql="SELECT COUNT(*) as qty,extract(YEAR from purchaseDate) as year,extract(MONTH from purchaseDate) as month,user.userName as userName " +
		"FROM Purchase " +
		"GROUP BY user.userName,extract(YEAR from purchaseDate),extract(MONTH from purchaseDate) " +
		"ORDER BY extract(YEAR from purchaseDate) DESC,extract(MONTH from purchaseDate) DESC,COUNT(*) DESC";
		
		// ESTE CODIGO SERVIRIA PARA NO TRAER TANTOS RESULTADOS Y PROCESAR EN JAVA PERO SQL NO ADMITE ESTA CONSULTA
		/* +
		"HAVING COUNT(*)>= ALL(" +
							"SELECT COUNT(*) as cant " +
							"FROM Purchase paux " +
							"WHERE p.year=paux.year " +
							"AND p.month=paux.month " +
							"GROUP BY paux.user.userName)";*/
							
		
		Query query = session.createQuery(hql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Iterator<Object> result = query.iterate();
		
		int actualMonth=0,actualYear=0;
		while(result.hasNext()) {
			Map<String, Object> row = (Map<String, Object>)result.next();
			if (!((Integer) row.get("month")==actualMonth && (Integer) row.get("year")==actualYear)){
				actualMonth=(Integer) row.get("month");
				actualYear=(Integer) row.get("year");
				report.add(new CustomerReportItem((String)row.get("userName"), (Long) row.get("qty"), actualMonth, actualYear));
			}
		}
		return report;
	}

	@SuppressWarnings("unchecked")
	public Set<CustomerReportItem> getMaxSell(){
		Set<CustomerReportItem> report = new HashSet<CustomerReportItem>();
		Session session = getSession();
		
		String hql="SELECT COUNT(*) as qty,extract(YEAR from purchaseDate) as year,extract(MONTH from purchaseDate) as month,item.user.userName as userName " +
		"FROM Purchase " +
		"GROUP BY item.user.userName,extract(YEAR from purchaseDate),extract(MONTH from purchaseDate) " +
		"ORDER BY extract(YEAR from purchaseDate) DESC,extract(MONTH from purchaseDate) DESC,COUNT(*) DESC";

		Query query = session.createQuery(hql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Iterator<Object> result = query.iterate();
		
		int actualMonth=0,actualYear=0;
		while(result.hasNext()) {
			Map<String, Object> row = (Map<String, Object>)result.next();
			if (!((Integer) row.get("month")==actualMonth && (Integer) row.get("year")==actualYear)){
				actualMonth=(Integer) row.get("month");
				actualYear=(Integer) row.get("year");
				report.add(new CustomerReportItem((String)row.get("userName"), (Long) row.get("qty"), actualMonth, actualYear));
			}
		}
		return report;
	}

	@SuppressWarnings("unchecked")
	public List<String> getComment(User user){
		Session session = getSession();	
		String hql = "SELECT comment FROM Purchase WHERE user=?";
		
		Query query = session.createQuery(hql);
		query.setParameter(0, user);
		List<String> results = (List<String>)query.list();
		return results;
	}

	@Override
	public List<Purchase> getConflictedPurchases() {
		String hql = "FROM Purchase as p " +
					"WHERE p.confirmation.buyerConfirmation = 'realize' AND " +
					"p.confirmation.sellerConfirmation = 'dontrealize' OR " +
					"p.confirmation.buyerConfirmation = 'dontrealize' AND " +
					"p.confirmation.sellerConfirmation = 'realize'";
		
		return find(hql);
	}

	@Override
	public List<Purchase> getDebtPendingPurchases() {
		String hql = "FROM Purchase as p " +
					"WHERE p.debtPending = true";
		
		return find(hql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IncomeReport> getIncomeReport(Date dateBefore, Date dateAfter) throws BigRangeException, ParseException {
		List<IncomeReport> ireport = new ArrayList<IncomeReport>();
		Map<Long, Double> map = new TreeMap<Long, Double>();
		Calendar c1 = Calendar.getInstance();
		c1.setTime(dateBefore);
		
		if (!validateRange(c1, dateBefore, dateAfter)) {
			throw new BigRangeException("Range exceeds one year limit");
		}
		
		//Lord Criteria
		Session session = getSession();
		Criteria crit = session.createCriteria(Purchase.class)
						.createAlias("confirmation", "c")
						.add(Restrictions.eq("debtPending", false))
						.add(Restrictions.eq("c.sellerConfirmation", Confirmated.realize))
						.add(Restrictions.eq("c.buyerConfirmation", Confirmated.realize))
						.add(Restrictions.between("purchaseDate", dateBefore, dateAfter))
						.setProjection(Projections.projectionList()
										.add(Projections.alias(Projections.sum("commission"), "monthincome"))
										.add(Projections.alias(Projections.sqlGroupProjection("extract(YEAR from purchaseDate) as year","year", 
												new String[]{"year"}, new Type[] {Hibernate.INTEGER}), "year"))
										.add(Projections.alias(Projections.sqlGroupProjection("extract(MONTH from purchaseDate) as month","month", 
												new String[]{"month"}, new Type[] {Hibernate.INTEGER}), "month")))
						.addOrder(Order.asc("year"))
						.addOrder(Order.asc("month"))
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		Iterator<Object> results = crit.list().iterator();
		
		while(results.hasNext()) {
			Map<String, Object> incomes = (Map<String, Object>)results.next();
			int year = (Integer) incomes.get("year");
			int month = ((Integer)incomes.get("month"))-1;
			c1.set(year, month, 1,0,0,0);
			long aux = c1.getTime().getTime();
			double income = (Double) incomes.get("monthincome");
			map.put(aux-aux%1000, income);
		}
		
		c1.setTime(dateBefore);
		c1.set(Calendar.DATE,1);
		while(c1.getTime().before(dateAfter) || c1.getTime().equals(dateAfter)){
			if(!map.containsKey(c1.getTime().getTime())){
				map.put(c1.getTime().getTime(),0.0);
			}
			c1.add(Calendar.MONTH, 1);
		}
		
		Set<Entry<Long,Double>> auxMap = map.entrySet();
		for(Entry<Long,Double> e: auxMap){
			c1.setTime(new Date(e.getKey()));
			String periodName = c1.getDisplayName(Calendar.MONTH, Calendar.LONG, Locale.US) + " " + String.valueOf(c1.get(Calendar.YEAR));
			ireport.add(new IncomeReport(periodName,e.getValue()));
		}
		
		return ireport;
	}
	
	private boolean validateRange(Calendar c, Date dateBefore, Date dateAfter) {
		int year = c.get(Calendar.YEAR);
		int daysTolerance;
		
		if (year % 400 == 0) {
			daysTolerance = 366;
		} else if (year % 100 == 0) {
			daysTolerance = 365;
		} else if (year % 4 == 0) {
			daysTolerance = 366;
		} else {
			daysTolerance = 365;
		}
		long totaldays = (-dateBefore.getTime()+dateAfter.getTime())/ (1000 * 60 * 60 * 24);
		if(totaldays>daysTolerance){
			return false;
		}
		return true;
	}
	
	public List<Purchase> getPendingConfirmationPurchases(int dayAmount){
		Timestamp date = new Timestamp(new Date().getTime()-dayAmount*(1000 * 60 * 60 * 24));
		String hql = "FROM Purchase as p " +
		"WHERE p.purchaseDate<? AND  (p.confirmation.sellerConfirmation = 'notyetconfirm' OR " +
		"p.confirmation.buyerConfirmation = 'notyetconfirm')";
		return find(hql, date);
	}
	
	public int getPositive(User user){
		String hql = "From Purchase AS p WHERE user = ? AND ( (p.confirmation.buyerConfirmation= 'realize' AND p.confirmation.sellerConfirmation = 'realize') OR" +
				" (p.confirmation.buyerConfirmation= 'dontrealize' AND p.confirmation.sellerConfirmation = 'dontrealize') )  AND p.confirmation.calification='positive'";
		List<Purchase> result = find(hql, user);
		return result.size();
	}
	
	public int getNegative(User user){
		String hql = "From Purchase AS p WHERE user = ? AND ( (p.confirmation.buyerConfirmation= 'realize' AND p.confirmation.sellerConfirmation = 'realize') OR" +
				" (p.confirmation.buyerConfirmation= 'dontrealize' AND p.confirmation.sellerConfirmation = 'dontrealize') )  AND p.confirmation.calification='negative'";
		List<Purchase> result = find(hql, user);
		return result.size();
	}
	
	public int getNeutral(User user){
		String hql = "From Purchase AS p WHERE user = ? AND ( (p.confirmation.buyerConfirmation= 'realize' AND p.confirmation.sellerConfirmation = 'realize') OR" +
				" (p.confirmation.buyerConfirmation= 'dontrealize' AND p.confirmation.sellerConfirmation = 'dontrealize') )  AND p.confirmation.calification='neutral'";
		List<Purchase> result = find(hql, user);
		return result.size();
	}

	@Override
	public void deletePurchase(Purchase purchase) {
		delete(purchase);
	}
}
